//加载express模块
const express = require('express');

//加载MYSQL模块
const mysql = require('mysql');
// 创建MYSQL连接池
const pool = mysql.createPool({
  host: '127.0.0.1',
  port: 3306,
  user: 'root',
  password: '',
  database: 'menu',
  connectionLimit: 20,
  charset: 'utf8'
});
//创建服务器
const server = express();
const md5 = require('md5')
//配置body-parser
const bodyParser = require('body-parser')
server.use(bodyParser.urlencoded({
  extended: false
}))
//加载CORS模块
const cors = require('cors');
server.use(cors({
  origin: ['http://localhost:8080', 'http://127.0.0.1:8080']
}));
/**************************************/
// 获取所有菜单分类的接口
server.get('/category_all', (req, res) => {
  //SQL语句以获取文章分类表的数据
  let sql = 'SELECT id,category_name FROM menu_category order by id';
  //执行SQL语句
  pool.query(sql, (error, results) => {
    if (error) throw error;
    res.send({ message: 'ok', code: 200, results: results })
  });
});
/*************************************/

// 获取首页菜单分类的接口
server.get('/category_home', (req, res) => {
  //SQL语句以获取文章分类表的数据
  let sql = 'SELECT id,category_name,image FROM home_category order by id';
  //执行SQL语句
  pool.query(sql, (error, results) => {
    if (error) throw error;
    res.send({ message: 'ok', code: 200, results: results })
  });
});
/*************************************/
// 获取导航栏分类下包含的菜单数据的接口
server.get('/index_all', (req, res) => {
  //获取客户端传递的cid参数
  let id = req.query.id;
  let sql = 'SELECT id,subject,main,content,image,user_id,category_id FROM menu_article WHERE category_id=?'
  pool.query(sql, [id], (error, results) => {
    if (error) throw error;
    res.send({ message: 'ok', code: 200, results: results })
  })
});
/*************************************/


// 获取首页小图标菜单数据的接口
server.get('/index_home', (req, res) => {
  //获取地址栏中的id参数
  let id = req.query.id;
  let sql = "SELECT r.id,subject,main,content,r.image,user_id,home_id FROM home_article AS r INNER JOIN home_category AS u ON home_id = u.id WHERE u.id=?";
  pool.query(sql, [id], (error, results) => {
    if (error) throw error;
    res.send({ message: "ok", code: 200, result: results })
  })
});
/*************************************/
// 获取热门推荐菜单数据的接口
server.get('/hot', (req, res) => {
  //获取地址栏中的id参数
  let sql = "SELECT id,subject,main,content,image,user_id FROM menu_hot";
  pool.query(sql, (error, results) => {
    if (error) throw error;
    res.send({ message: "ok", code: 200, result: results })
  })
});
/*************************************/
// 根据搜索关键字，找到对应的菜名
server.get('/search_name', (req, res) => {
  // 获取用户搜索的内容
  let search = req.query.search;
  let sql = "SELECT id,subject,main,content,image,user_id FROM menu_article WHERE subject LIKE ? ";
  pool.query(sql, [search], (err, result) => {
    if (err) throw err;
    res.send({ message: 'ok', code: 200, result: result })
  })
})
/************************************************* */
// 验证是否收藏
server.get('/collected',(req,res)=>{
  let uid = req.body.uid;
  let menu_id = req.body.menu_id;
  let sql = 'select count(id) as count from user_collect where uid=? and menu_id=?'
  pool.query(sql, [uid, menu_id], (err, result) => {
    if (err) throw err;
    res.send({message:'ok',code:200,result:result})
  })
})
/*************************************************** */
// 收藏接口
server.get('/collect_show', (req, res) => {
  let id = req.query.id;
  console.log(id)
  let sql = "SELECT r.id,subject,main,content,r.image,user_id,category_id FROM menu_article AS r INNER JOIN user_collect AS u ON r.id = u.menu_id WHERE u.uid=?"
  pool.query(sql, [id], (err, result) => {
    if (err) throw err;
    res.send({ message: 'ok', code: 200, result: result })

  })
})
/******************************************* */
// 添加收藏
server.post('/insert_col', (req, res) => {
  let uid = req.body.uid;
  let menu_id = req.body.menu_id;
  let sql = 'select count(id) as count from user_collect where uid=? and menu_id=?'
  pool.query(sql, [uid, menu_id], (err, result) => {
    if (err) throw err;
    let count = result[0].count;
    if (count == 0) {
      let sql = "insert into user_collect values(null,?,?)"
      console.log(uid)
      console.log(menu_id)
      pool.query(sql, [uid, menu_id], (err, result) => {
        if (err) throw err;
        res.send({ message: 'ok', code: 200, result: result })
      })
    } else {
      res.send({ message: 'user exists', code: 201 });
    }
  })
}) /*********************************************************** */
  // 取消收藏
  server.post('/del', (req, res) => {
    let uid = req.body.uid;
    let menu_id = req.body.menu_id;
    let sql = 'delete from user_collect where uid=? and menu_id=?'
    pool.query(sql, [uid, menu_id], (err, result) => {
      if (err) throw err;
      res.send({ message: 'ok', code: 200, result: result })
    })
  })
// ******************************************************************
//获取详情页文章的数接口
server.get('/detail', (req, res) => {
  //获取地址栏中的id参数
  let id=req.query.id;
  //sql查询
  let sql = "select a.id,subject,main,assist,content,image,nickname,avatar,article_number from menu_article as a inner join menu_user as u on user_id=u.id where a.id=?;"
  //执行sql查询
  pool.query(sql, [id], (error, results) => {
    if (error) throw error;
    //返回客户端
    res.send({ message: 'ok', code: 200, results: results[0] })
  })
})
/*********************************************************************** */
// 注册接口
server.post('/register', (req, res) => {
  console.log(md5('123456'))
  // 获取用户名和密码
  let username = req.body.username;
  let password = req.body.password;
  let sql = 'select count(id) as count from menu_user where username=?'
  pool.query(sql, [username], (error, results) => {
    if (error) throw error;
    let count = results[0].count;
    if (count == 0) {
      sql = 'insert menu_user(username,password) value(?,MD5(?))';
      pool.query(sql, [username, password], (error, results) => {
        if (error) throw error;
        res.send({ message: 'ok', code: 200 });
      })
    } else {
      res.send({ message: 'user exists', code: 201 });
    }
  })
})
// 登录接口
server.post('/login', (req, res) => {
  let username = req.body.username;
  let password = req.body.password;
  let sql = 'select id,username,nickname,avatar from menu_user where username=? and password=MD5(?)'
  pool.query(sql, [username, password], (error, results) => {
    if (error) throw error;
    if (results.length == 0) {
      res.send({ message: 'login failed', code: 201 })
    } else {
      res.send({ message: 'ok', code: 200, result: results[0] })
    }
  })
})
// 端口号
server.listen(3000, () => {
  console.log('server is running...');
});